from pyecharts.charts import Bar
from pyecharts import options as opts
from sc.ZDao import getConnect


def generate_salary_chart():
    # 连接数据库
    db = getConnect()
    cur = db.cursor()

    # 查询语句 - 获取城市级别的平均工资（去除区县级别数据）
    sql = """
    SELECT 
        CASE 
            WHEN job_loc LIKE '%·%' THEN SUBSTRING_INDEX(job_loc, '·', 1)
            ELSE job_loc
        END AS city,
        ROUND(AVG(job_sal), 2) AS avg_salary
    FROM jobs
    GROUP BY city
    ORDER BY avg_salary DESC
    """

    cities = []
    salaries = []

    try:
        cur.execute(sql)
        res = cur.fetchall()

        for r in res:
            city = r['city'] if isinstance(r, dict) else r[0]
            salary = float(r['avg_salary'] if isinstance(r, dict) else r[1])

            cities.append(city)
            salaries.append(salary)

        print("成功获取数据：")
        print("城市列表:", cities)
        print("平均工资:", salaries)

    except Exception as e:
        print(f"数据库操作失败: {e}")
        db.rollback()
        return
    finally:
        if db:
            db.close()

    # 创建柱状图
    bar = Bar(init_opts=opts.InitOpts(
        width="1000px",
        height="600px",
        page_title="各城市平均工资对比"
    ))

    # 添加数据
    bar.add_xaxis(cities)
    bar.add_yaxis(
        series_name="平均月薪(元)",
        y_axis=salaries,
        label_opts=opts.LabelOpts(
            is_show=True,
            position="top",
            formatter="{c}元",
            font_size=12,
            color="#333"
        ),
        itemstyle_opts=opts.ItemStyleOpts(
            color="#5470c6"
        )
    )

    # 设置全局选项
    bar.set_global_opts(
        title_opts=opts.TitleOpts(
            title="各城市平均工资对比",
            subtitle="数据来源: xm数据库jobs表 | 单位: 元",
            pos_left="center",
            title_textstyle_opts=opts.TextStyleOpts(
                font_size=18,
                color="#333"
            )
        ),
        xaxis_opts=opts.AxisOpts(
            axislabel_opts=opts.LabelOpts(
                rotate=45,
                font_size=12,
                margin=10
            ),
            name="城市",
            name_location="middle",
            name_gap=30,
            name_textstyle_opts=opts.TextStyleOpts(
                font_size=14
            )
        ),
        yaxis_opts=opts.AxisOpts(
            name="平均月薪",
            name_location="middle",
            name_gap=50,
            axislabel_opts=opts.LabelOpts(
                formatter="{value} 元",
                font_size=12
            ),
            splitline_opts=opts.SplitLineOpts(
                is_show=True,
                linestyle_opts=opts.LineStyleOpts(
                    width=1,
                    type_="dashed",  # 修正这里，使用type_而不是type
                    opacity=0.5
                )
            )
        ),
        tooltip_opts=opts.TooltipOpts(
            trigger="axis",
            axis_pointer_type="shadow",
            formatter="{b}<br/>平均工资: {c}元"
        ),
        datazoom_opts=[
            opts.DataZoomOpts(
                type_="slider",
                xaxis_index=0,
                range_start=0,
                range_end=100
            ),
            opts.DataZoomOpts(
                type_="inside",
                xaxis_index=0,
                range_start=0,
                range_end=100
            )
        ],
        toolbox_opts=opts.ToolboxOpts(
            is_show=True,
            feature={
                "saveAsImage": {"title": "保存图片"},
                "restore": {"title": "重置"},
                "dataView": {"title": "数据视图"}
            }
        )
    )

    # 渲染图表
    bar.render("city_salary_comparison.html")
    print("图表已生成: city_salary_comparison.html")


if __name__ == "__main__":
    generate_salary_chart()
